<?php

/***************************************************************\
* FILE:		sys/pages/maintenance.inc
* SECTION:	pages > db maintenance
* FUNCTION:	backup, optimize and restore
* USES:	
* INCLUDES:		
* PRODUCES:	
* LAUNCHES:	
\***************************************************************/

/**
 * adds extra access checks, dependent on page state
 */
function page_inc_check_access() {
	check_access("ADMIN");
}

function page_inc_execute_action($action) {
	global $CONFIG;
	switch($action) {
		case "backup":
			echo "<html><head><title>Backup process</title></head><body><h1>Backup process</h1><h3>stage 1: writing backup</h3>";
			
			// dumping data
			$filename = $CONFIG['backup_dir'] . "/" . date("YmdHis") . ".sql";
			$command = "mysqldump -e -h {$CONFIG['db_host']} -u {$CONFIG['db_user']} -p{$CONFIG['db_pwd']} -r $filename {$CONFIG['db_name']}";
			echo "<p>issuing dump command <pre>$command</pre>";
			echo "<p>SKIPPING DUMP --> USE MYSQLMYADMIN !";
			echo "<p>finished writing";

			// cleaning up tables (deleting obsolete rows)
			echo "<h3>Cleaning up tables</h3>";
			$sql = db_query_select(array(array('item_id')), array('ITEM'), "deleted = 0");
			$sqlresult = db_d_query($sql);
			$idstring = "-1";
			while ($id = db_fetch_array($sqlresult))
				$idstring .= "," . $id['item_id'];
			$where = "item_id NOT IN ({$idstring})";
			echo "<p>Deleting obsolete rows from HAS_READ";
			db_d_query(db_query_delete("HAS_READ", $where));
			echo "<p>Deleting obsolete rows from ITEM_IN";
			db_d_query(db_query_delete("ITEM_IN", $where));
			echo "<p>Deleting obsolete rows from ARTICLE";
			db_d_query(db_query_delete("ARTICLE", $where));
			echo "<p>Deleting obsolete rows from LINK";
			db_d_query(db_query_delete("LINK", $where));
			echo "<p>Deleting obsolete rows from NEWS";
			db_d_query(db_query_delete("NEWS", $where));
			echo "<p>Deleting obsolete rows from AD";
			db_d_query(db_query_delete("AD", $where));
			echo "<p>Deleting obsolete rows from DOWNLOAD";
			db_d_query(db_query_delete("DOWNLOAD", $where));
			echo "<p>Deleting obsolete rows from GALLERY";
			db_d_query(db_query_delete("GALLERY", $where));
			echo "<p>Deleting obsolete rows from ART";
			db_d_query(db_query_delete("ART", $where));
			echo "<p>Deleting obsolete rows from POLL_VOTE";
			db_d_query(db_query_delete("POLL_VOTE", $where));
			echo "<p>Deleting obsolete rows from POLL_ANSWER";
			db_d_query(db_query_delete("POLL_ANSWER", $where));

			echo "<p>Deleting obsolete rows from table ITEM";
			$sql = db_query_delete("ITEM", "deleted = 1");
			db_d_query($sql);
			
			echo "<p>Deleting obsolete rows from POST tables";

			$sql = db_query_select(array(array('post_id')), array('POST'), "deleted = 0 AND item_id IN ($idstring)");
			$sqlresult = db_d_query($sql);
			$idstring = "-1";
			while ($id = db_fetch_array($sqlresult))
				$idstring .= "," . $id['post_id'];
			$where = "post_id NOT IN ({$idstring})";
			
			$sql = db_query_delete("POST_BODY", $where);
			db_d_query($sql);
			$sql = db_query_delete("POST", $where);
			db_d_query($sql);
			
			
			// restore num_posts
			echo "<p>Restoring num_posts statistic in ITEM table";
			$sqlresult = db_do_query("SELECT ITEM.item_id AS item_id, num_posts, COUNT(post_id) AS real_posts FROM ITEM, POST WHERE ITEM.item_id = POST.item_id GROUP BY ITEM.item_id HAVING real_posts - num_posts != 0");
			while ($r = @db_fetch_array($sqlresult)) {
				echo "<li>correcting postcount for item {$r['item_id']}";
				db_do_query("UPDATE ITEM SET num_posts = {$r['real_posts']}, creation = creation, edit = edit WHERE item_id = {$r['item_id']}");
			}

			// delete obsolete subsets
			echo "<p>Deleting obsolete subsets";
			$sqlresult = db_do_query("SELECT SUBSET.sub_id, COUNT(item_id) AS itemcount FROM SUBSET LEFT JOIN ITEM_IN " . 
						 "ON SUBSET.sub_id = ITEM_IN.sub_id GROUP BY SUBSET.sub_id HAVING itemcount = 0");
			$subidstring = "-1";
			while ($r = @db_fetch_array($sqlresult))
				$subidstring .= "," . $r['sub_id'];
			db_do_query("DELETE FROM SUB_IN WHERE sub_id IN ($subidstring)");
			db_do_query("DELETE FROM SUBSET WHERE sub_id IN ($subidstring)");
			

			// delete mails !!
			echo "<p>Deleting obsolete rows from MAIL tables";

			$sql = db_query_select(array(array('mail_id')), array('MAIL_HEADER'), "deleted = 0");
			$sqlresult = db_d_query($sql);
			$idstring = "-1";
			while ($id = db_fetch_array($sqlresult))
				$idstring .= "," . $id['mail_id'];
				
			$sql = db_query_select(array(array('mail_id')), array('MAIL_TO'), "deleted = 0");
			$sqlresult = db_d_query($sql);
			while ($id = db_fetch_array($sqlresult))
				$idstring .= "," . $id['mail_id'];
				
			$where = "mail_id NOT IN ({$idstring})";
			
			$sql = db_query_delete("MAIL_HEADER", $where);
			db_d_query($sql);
			$sql = db_query_delete("MAIL_BODY", $where);
			db_d_query($sql);
			$sql = db_query_delete("MAIL_TO", $where);
			db_d_query($sql);
			
			
			
			echo "<p>finished cleaning";
			
			// optimizing tables
			echo "<h3>Optimizing tables</h3>";
			$sql = "SHOW TABLES";
			$sqlresult = db_d_query($sql);
			echo "<p>";
			while (list($t) = mysql_fetch_row($sqlresult)) {
				echo "optimizing table $t<br>";
				db_d_query("OPTIMIZE TABLE $t");
			}
			echo "<p>finished optimizing";
			die("<p>FINISHED MAINTENANCE<p><a href=/home/.admin>terug naar administratiepagina</a></body></html>");
			break;
		default:
			add_alert("unknown action: $action");
			break;
	}
}

/**
 * execute page logic
 */
function page_inc_execute() {
	global $PAGE, $CONFIG;
	$CONTENT = array();

	$CONTENT['title'] = "Onderhoud van de database";
	$CONTENT['body'] = "<a href=.maintenance?action=backup>klik hier</a> om een backup van de database te maken, of selecteer hieronder een backup om te herstellen";

	if ($_POST['submitted'] and ($_POST['restore_action'] == "restore")) {
		if ($_POST['confirmed']) {
			$sql = "SHOW TABLES";
			$sqlresult = db_d_query($sql);
			while (list($t) = mysql_fetch_row($sqlresult)) {
				db_d_query("DROP TABLE $t");
			}
			system("mysql -h {$CONFIG['db_host']} -u {$CONFIG['db_user']} -p{$CONFIG['db_pwd']} {$CONFIG['db_name']} -e \"source {$_POST['file']}\"");
			add_alert("backup {$_POST['file']} hersteld");
		} else
			$CONTENT['form'] = form_confirm("bent u zeker dat u {$_POST['file']} wil herstellen ? (ALLE GEGEVENS DIE HIERNA WERDEN INGEVOERD, GAAN VERLOREN)");
	} else if ($_POST['submitted'] and ($_POST['restore_action'] == "get_file")) {
		redirect("/SAI" . "/".$_POST['file']);
	} else {
		$ks = array();
		$vs = array();
		$dir = $CONFIG['backup_dir'];
		if (!is_dir($dir))
			die("backup dir not set in config file");
		if ($dh = opendir($dir))
			while (($file = readdir($dh)) !== false)
				if (ereg("^[0-9]{14}\.sql$", $file)) {
					$ks[] = "$dir$file";
					$vs[] = "backup van " . substr($file,6,2) . "/" . substr($file,4,2). "/" . substr($file,0,4);
				}
		closedir($dh);
		$inputs = array();
		$inputs[] = form_input_select("Actie", "restore_action", array("get_file", "restore"), array("backup downloaden", "backup herstellen"), "kies een actie");
		$inputs[] = form_input_select("Backup", "file", $ks, $vs, "selecteer een backup");
		$CONTENT['form'] = form_create("restoreform", $inputs);
	}

	$PAGE['content'] = $CONTENT;
}


/**
 * dress up page content using skin stuff
 */
function page_inc_dress() {
	global $PAGE;
	$CONTENT = $PAGE['content'];
	if ($CONTENT['body'])
		print_body($CONTENT['body']);
	if ($CONTENT['form'])
		print_form($CONTENT['form']);
}

function db_d_query($sql) {
	echo "<blockquote>query: <pre>$sql</pre></blockquote>";
	return db_do_query($sql);
}

?>
